#!/usr/bin/env python
#coding=utf-8
# noinspection PyInterpreter
import os
import sys
reload(sys)
sys.setdefaultencoding('utf8')


from openpyxl import load_workbook


import xlwt
# import MySQLdb
import pymysql
# conn=MySQLdb.connect(host='rm-uf6fzi1myaxtd1c6ao.mysql.rds.aliyuncs.com',user='liangjun',passwd='LIANGJUN@well_talent',db='well_dev')
from sshtunnel import SSHTunnelForwarder
import config

# dbConfig = {
#     'host': '192.168.9.125',
#     'user': 'test',
#     'passwd': 'test123',
#     'port': 3306,
#     'db': 'dm_website'
# }
dbConfig = config.dbConfig


def getDB(dbConfig):
    # dbConfig = eval(dbConfigName)
    try:
        server = SSHTunnelForwarder(
            ssh_address_or_host=('192.168.9.125', 22),  # 指定ssh登录的跳转机的address
            ssh_username='root',  # 跳转机的用户
            ssh_password='dm2018dm',  # 跳转机的密码
            remote_bind_address=('127.0.0.1', 3306))
        server.start()
        print server.local_bind_port
        conn = pymysql.connect(host='127.0.0.1', user=dbConfig['user'], passwd=dbConfig['passwd'],
                               port=server.local_bind_port, db=dbConfig['db'])
        # conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8")
        curr.execute("USE %s" % dbConfig['db'])

        return conn, curr
    except pymysql.Error, e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

def getTables():
    # conn = pymysql.connect(dbConfig)#(host='rm-uf6fzi1myaxtd1c6ao.mysql.rds.aliyuncs.com', user='liangjun',passwd='LIANGJUN@well_talent', db='well_dev')
    # cursor = conn.cursor()
    # conn,cursor = getDB(dbConfig)
    # tables = cursor.execute('show tables;')
    # data = cursor.fetchall()

    selectSql = "select TABLE_NAME tabName from information_schema.TABLES where TABLE_SCHEMA='{schemaName}';".format(schemaName=dbConfig["db"])
    conn, curr = getDB(dbConfig)
    curr.execute(selectSql)
    datas = curr.fetchall()
    # print datas

    return datas

def tablesJegou(tableName):
    # 边框的定义
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    borders.bottom_colour = 0x3A
    # Initialize a style for frist row
    style_fristRow = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True
    font.colour_index = 1
    style_fristRow.font = font

    badBG = xlwt.Pattern()
    badBG.pattern = badBG.SOLID_PATTERN
    badBG.pattern_fore_colour = 6
    style_fristRow.pattern = badBG

    style_fristRow.borders = borders

    # Initialize a style for data row
    style_dataRow = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = u'隶变-简 常规体'
    font.bold = False
    style_dataRow.font = font

    style_dataRow.borders = borders
    # print dbConfig["db"],tableName
    selectSql = "SELECT ORDINAL_POSITION colNo, \
                COLUMN_NAME colName, \
                COLUMN_TYPE dataType, \
                IS_NULLABLE isNull, \
                COLUMN_COMMENT colComment \
            FROM information_schema. COLUMNS \
            WHERE TABLE_SCHEMA = '{schemaName}' \
            AND table_name = '{tabName}';".format(schemaName=dbConfig["db"], tabName=tableName)

    # print selectSql

    conn, curr = getDB(dbConfig)
    curr.execute(selectSql)
    datas = curr.fetchall()
    fields = curr.description

    # -- developerclub为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
    tableSelect = "SELECT TABLE_NAME,TABLE_COMMENT  \
                FROM INFORMATION_SCHEMA.TABLES \
                WHERE table_schema ='{schemaName}' \
                AND table_name = '{tableName}'".format(schemaName=dbConfig["db"],tableName=tableName)
    curr.execute(tableSelect)
    dataTable = curr.fetchall()
    fieldTable = curr.description

    workbook = xlwt.Workbook()
    # workbook = load_workbook(filename=r'{exportPath}/{exportName}.xls'.format(exportPath=dbConfig["host"], exportName=dbConfig["db"]))
    sheet = workbook.add_sheet('{tabName}'.format(tabName=tableName),True)
    # sheet = workbook.add_sheet(tableName,True)

    # print sheet

    FullTableName = dataTable[0][0] + "------" + dataTable[0][1]
    # print FullTableName
    sheet.write(0,0,unicode(FullTableName,"utf-8"))

    # 写上字段信息
    #todo 行数从第二行开始，第一行表名，
    for field in range(0, len(fields)):
        sheet.write(1, field, fields[field][0], style_fristRow)

    # 获取并写入数据段信息
    # row = 1
    rowstart = 1
    col = 0
    for row in range(1, len(datas) + 1):
        for col in range(0, len(fields)):
            # print datas[row - 1][col], style_dataRow
            sheet.write(row+rowstart, col, u'%s' % datas[row - 1][col], style_dataRow)
    path = dbConfig["db"].strip()
    # 去除尾部 \ 符号
    path = path.rstrip("\\")

    # 判断路径是否存在
    # 存在     True
    # 不存在   False
    isExists = os.path.exists(path)
    # 判断结果
    if not isExists:
        # 如果不存在则创建目录
        # 创建目录操作函数
        os.makedirs(path)

        # print path + ' 创建成功'
        # return True
    # else:
        # 如果目录存在则不创建，并提示目录已存在
        # print path + ' 目录已存在'
        # return False

    workbook.save(r'{exportPath}/{exportName}.xls'.format(exportPath=dbConfig["db"], exportName=tableName))

    curr.close()
    conn.close()
def main():
    TABLES = getTables()
    # print TABLES
    for table in TABLES:
        print table
        # if table[0] == "course":
        #     break
        print table[0]
        tablesJegou(table[0])
        # workbook.save(r'{exportPath}/{exportName}.xls'.format(exportPath=dbConfig["host"], exportName=dbConfig["db"]))


main()

# count = cursor.execute('select * from ')
# print 'has %s record' % count
# #重置游标位置
# cursor.scroll(0,mode='absolute')
# #搜取所有结果
# results = cursor.fetchall()
# #测试代码，print results
# #获取MYSQL里的数据字段
# fields = cursor.description
# #将字段写入到EXCEL新表的第一行
# wbk = xlwt.Workbook()
# sheet = wbk.add_sheet('test1',cell_overwrite_ok=True)
# for ifs in range(0,len(fields)):
#     sheet.write(0,ifs,fields[ifs][0])
# ics=1
# jcs=0
# for ics in range(1,len(results)+1):
#     for jcs in range(0,len(fields)):
#         sheet.write(ics,jcs,results[ics-1][jcs])
# wbk.save('/databaseTables.xlsx')